import pandas as pd
import numpy as np
import datetime
from sqlalchemy import create_engine
from sqlalchemy.pool import NullPool
import os
if os.getenv('AG_ENV_PYTHON_PATH') is None:
    ROOT_DIR = os.path.dirname(os.path.abspath(__file__)) + os.sep
else:
    ROOT_DIR = os.getcwd() + os.sep
    # ROOT_DIR = WORK_DIR + "ag_art" + os.sep
parent_dir = os.path.dirname(ROOT_DIR)
import importlib.util
abstractjob_path = parent_dir+'/SKO/AbstractDPJob.py'
spec = importlib.util.spec_from_file_location('AbstractDPJob', abstractjob_path)
abstractjob_module = importlib.util.module_from_spec(spec)
spec.loader.exec_module(abstractjob_module)
AbstractDPJob = abstractjob_module.AbstractDPJob
# from SKO.AbstractDPJob import AbstractDPJob
class Predict_6635Job(AbstractDPJob):
    def __init__(self,
                 p_lg_no=None, p_start_date=None, p_end_date=None):

        super(Predict_6635Job, self).__init__()
        self.lg_no = p_lg_no
        self.start_date = p_start_date
        self.end_date = p_end_date
        pass


    def execute(self):
        return self.do_execute()


    def do_execute(self):
        super(Predict_6635Job, self).do_execute()
        result_list = []
        #预测铁水硫接口传入参数
        lg_no = self.lg_no
        start_date = self.start_date
        end_date = self.end_date
        DB_HOST_MPP_DB2 = '10.70.48.41'
        DB_PORT_MPP_DB2 = 50021
        DB_DBNAME_MPP_DB2 = 'BGBDPROD'
        DB_USER_MPP_DB2 = 'm1admin'
        DB_PASSWORD_MPP_DB2 = 'm1adminbdzg'

        def getConnectionDb2(host, port, dbname, user, password):
            # conn = pg.connect(host=host, port=port, dbname=dbname, user=user, password=password)
            engine = create_engine('ibm_db_sa://' + user + ':' + password + '@' + host + ':' + str(port) + '/' + dbname,
                                   encoding="utf-8", poolclass=NullPool)
            return engine.connect()

        db_conn_mpp = getConnectionDb2(DB_HOST_MPP_DB2,
                                       DB_PORT_MPP_DB2,
                                       DB_DBNAME_MPP_DB2,
                                       DB_USER_MPP_DB2,
                                       DB_PASSWORD_MPP_DB2)
        if lg_no == 'LG1':
            print(1)
            sql_condition = "and DATA_SOURCE_FACTORY = 'LG1'"
        elif lg_no == 'LG2':
            print(2)
            sql_condition = "and DATA_SOURCE_FACTORY = 'LG2'"
        else:
            print(0)
            sql_condition = ""

        sql = " SELECT  " \
              " ST_NO,COUNT_X,AIM_ST_S, " \
              " case  " \
              " when left(ST_NO,2) not in ('IW','IH') AND AIM_ST_S <= 15 AND COUNT_X = 2 " \
              " then 1 " \
              " when left(ST_NO,2) not in ('IW','IH') AND AIM_ST_S > 15 AND AIM_ST_S <= 20 AND COUNT_X = 2 " \
              " then 2 " \
              " when left(ST_NO,2) not in ('IW','IH') AND AIM_ST_S > 20 AND AIM_ST_S <= 30 AND COUNT_X = 2 " \
              " then 3 " \
              " when left(ST_NO,2) not in ('IW','IH')  AND AIM_ST_S > 30 AND COUNT_X = 2 " \
              " then 4 " \
              " when left(ST_NO,2) not in ('IW','IH')  AND AIM_ST_S <= 50 AND COUNT_X = 1 " \
              " then 5 " \
              " when left(ST_NO,2) not in ('IW','IH') AND AIM_ST_S > 50 AND AIM_ST_S <= 100 AND COUNT_X = 1 " \
              " then 6 " \
              " when left(ST_NO,2) not in ('IW','IH') AND AIM_ST_S > 100 AND AIM_ST_S < 150 AND COUNT_X = 1 " \
              " then 7 " \
              " when left(ST_NO,2) not in ('IW','IH') AND AIM_ST_S >= 150 AND AIM_ST_S < 180 AND COUNT_X = 1 " \
              " then 8 " \
              " when left(ST_NO,2) not in ('IW','IH') AND AIM_ST_S >= 180 AND AIM_ST_S <= 250 AND COUNT_X = 1 " \
              " then 9 " \
              " when left(ST_NO,2) not in ('IW','IH') AND AIM_ST_S > 250 AND COUNT_X = 1 " \
              " then 10 " \
              " when ST_NO ='IH2554A2'  " \
              " then 11 " \
              " when left(ST_NO,2) = 'IH' and ST_NO !='IH2554A2'  " \
              " then 12 " \
              " when left(ST_NO,2) = 'IW' " \
              " then 13 " \
              " else 14 " \
              " end as GROUP_X " \
              " from( " \
              " select a.ST_NO, " \
              " a.COUNT_X, " \
              " b.ELM_S_MAX*10000 as AIM_ST_S " \
              " FROM " \
              " (select " \
              " CASE  " \
              " WHEN FIN_ST_NO != '' " \
              " THEN FIN_ST_NO " \
              " WHEN FIN_ST_NO = '' and DECI_ST_NO != '' " \
              " THEN DECI_ST_NO " \
              " ELSE PREC_ST_NO " \
              " END AS ST_NO, " \
              " CASE  " \
              " WHEN REFINE_4 != '' " \
              " THEN 2 " \
              " WHEN REFINE_4 = '' AND REFINE_3 != '' " \
              " THEN 2 " \
              " WHEN REFINE_4 = '' AND REFINE_3 ='' AND REFINE_2 != '' " \
              " THEN 2 " \
              " WHEN REFINE_4 = '' AND REFINE_3 = '' AND REFINE_2 = '' AND REFINE_1 != '' " \
              " THEN 1 " \
              " ELSE 0 " \
              " END AS COUNT_X " \
              " from " \
              " BGTAMSZZ00.T_DWD_FACT_ZZPQ_QM_CR_SM_PATH " \
              " where TAP_END_TIME>='%s' " \
              " and TAP_END_TIME <'%s' " \
              " %s " \
              " )as a " \
              " left join " \
              " ( " \
              " select ST_NO,ELM_S_MAX " \
              " from " \
              " BGTAMOQMTM.T_ODS_TQMTMS1 " \
              " where line_dif='1')as b " \
              " on a.ST_NO=b.ST_NO " \
              " where COUNT_X != 0 " \
              " ) " % (start_date, end_date, sql_condition)
        print(sql)
        df0 = pd.read_sql_query(sql, con=db_conn_mpp)
        df0.columns = df0.columns.str.upper()
        df1 = df0[df0['GROUP_X'] != 14]
        df1 = df1.reset_index(drop=True)
        df_out = df1.groupby('GROUP_X').size().reset_index(name='count')
        df_out['GROUP_X'] = df_out['GROUP_X'].astype(int)
        def __cal_rule(x):
            rst = ''
            if x.GROUP_X == 1:
                rst = '双重处理,S≤15ppm'
            elif x.GROUP_X == 2:
                rst = '双重处理,15＜S≤20ppm'
            elif x.GROUP_X == 3:
                rst = '双重处理,20＜S≤30ppm'
            elif x.GROUP_X == 4:
                rst = '双重处理,S＞30ppm'
            elif x.GROUP_X == 5:
                rst = '单重处理,S≤50ppm'
            elif x.GROUP_X == 6:
                rst = '单重处理,50＜S≤100ppm'
            elif x.GROUP_X == 7:
                rst = '单重处理,100＜S＜150ppm'
            elif x.GROUP_X == 8:
                rst = '单重处理,150≤S＜180ppm'
            elif x.GROUP_X == 9:
                rst = '单重处理,180≤S≤250ppm'
            elif x.GROUP_X == 10:
                rst = '单重处理,S>250ppm'
            elif x.GROUP_X == 11:
                rst = 'IH2554A2'
            elif x.GROUP_X == 12:
                rst = 'IH其他取向硅钢'
            elif x.GROUP_X == 13:
                rst = 'IW'
            return rst
        df_out['RULE'] = df_out.apply(lambda x: __cal_rule(x), axis=1)
        sum_count = df_out['count'].sum()
        df_out['RATIO'] = df_out['count'] / sum_count * 100

        st_no_list = []
        for index, row in df_out.iterrows():
            group_tmp = row['GROUP_X']
            df2 = df1[df1['GROUP_X'] == group_tmp]
            df2 = df2.reset_index(drop=True)
            st_list = df2['ST_NO'].tolist()
            unique_list = list(dict.fromkeys(st_list))
            print(unique_list)
            st_no_list.append(str(unique_list))
            print('stop')
        df_out['ST_NO'] = st_no_list
        result_list = df_out.to_dict(orient='records')
        print('finish')
        return result_list
